1. Field of Invention
The present invention relates to systems and methods for manipulation of real time data, and more particularly, to systems and methods for manipulation of real time data in a spreadsheet.
2. Background of Invention
A spreadsheet application provides an environment where formulas may be entered manually into tabular arrays by the user to define various functions, totals, databases, and graphs. One common usage of spreadsheet applications is to store and operate on time series data. Such operations typically include graphing, computing averages, standard deviations, or other statistical measures. In order to do this, conventional spreadsheets generally need all of the data that define the time series to be present in the spreadsheet.
By design, conventional spreadsheet applications operate on the current values of all variables stored in the spreadsheet's memory partition. Any time there is change in the value of a variable in a spreadsheet cell--for example by the user inputting a new value--the spreadsheet often recalculates the value of every cell that is effected by the change in value, and may recalculate all cells in the spreadsheet. Thus, for a given variable, the spreadsheet always, and only, operates on a current value of the variable, and conventional spreadsheets are not designed to operate on the historical values a variable or cell has had over time.
In a spreadsheet environment, it is now common to import and use real-time data items from external real time data sources. In particular, means are commonly provided to import "real time data items" ("RTDIs") into individual spreadsheet cells, such RTDIs frequently being numeric data generated by an external physical process. In general, such RTDIs appear within spreadsheets as simple numeric values which change from time to time. These data items are "real-time," in the sense that they reflect the almost-current state of the process which they attempt to describe.
For example, for spreadsheet users concerned with financial markets, RTDIs specifying prices or interest rates describing various markets are typically imported by reference into a spreadsheet cell from an external computer program that is linked over a computer network to a computer system providing the real time information. In manufacturing process control, the RTDI might involve numerical data specifying the changing position of a tool part, the temperature of a liquid, or the air speed in a wind tunnel. Further, real-time calculations applied to an RTDI may yield other RTDIs, for example, squaring the value of an RTDI, or converting a temperature from Fahrenheit to Celsius, and these results also be further cascaded to yield other RTDIs.
In general then, RTDIs are time dependent data values that are generated in or near real time by some source external to the spreadsheet application. These RTDIs are then imported into a cell of a spreadsheet, and form the basis of some calculation or graph. As noted, conventional spreadsheets update themselves each time the value of cell changes, and only store the current value of a cell. Where a RTDI is imported into a single cell, the spreadsheet will replace the previous value of the RTDI stored in the cell. Any previous value of the RTDI is effectively lost each time a new RTDI is received.
This limitation of conventional spreadsheets is not always desirable. In order to compute various statistical measures on a sequence of RTDIs, or to plot the sequence over time, it is instead desirable to store and use prior values of an RTDI in addition to its current value, in the form of time series data or other reduced formats of data.
For example, suppose that the user desires to compute the time-averaged value of an RTDI. In this case, the current value of the RTDI is insufficient information to compute the desired result. In addition to the current value of the RTDI, there must also be available to the spreadsheet application either (A) all of the prior RTDI values and the times at which these appeared, or (B) sufficient summary information derived from such prior values which permits the desired quantity to be computed on the basis of this summary information, the last-computation time, and the current time and the current value of the RTDI. The former technique above is termed herein the "time-series" strategy, since it employs a full sequence of (time, data value(s)) tuples upon which to base the computation of the desired quantity. The latter approach is termed the "incremental" strategy herein since it exploits the specific computational structure of the desired quantity to reduce the amount of information which is required to complete the computation. Thus, in the current example, when computing the time-averaged value of the RTDI, it is sufficient in the incremental strategy to employ solely the previously computed average, the time it was last computed, the current value of the RTDI, and the current time. Accordingly, if only the current value of the RTDI is available, and no other data about the previous values is available, then it is impossible to compute a time dependent value or plot the changes in the RTDI over time without further mechanisms.
Within the spreadsheet environment, the standard state of the art for carrying out the time-series strategy is to create a complex and individually-designed macro command which organizes and displays the RTDI sequence, invoked upon each new RTDI received into the spreadsheet application. This approach however leaves it up to the individual user to program the macro, a non-trivial task for most users. Moreover, this approach, because it relies on the spreadsheet to interpret and execute a macro, results in relatively slow operation, and is unsuitable for complex computations which require fast computation. Also, because there are many different types of functions for which time series computations are desired--averages, volatility, standard deviations, drift to name a few--the user would have to individually program each desired function separately, a time consuming and burdensome task, and one beyond the capability of most spreadsheet users.
There are generally also no standard means for the incremental strategy, and in this case as well a new and complex macro would normally be individually designed for each specific incremental calculation desired.
Accordingly, it is desirable to provide within a spreadsheet application, functions and data structures that can store and manipulate real time data items into time series data, enabling the spreadsheet application to receive and store RTDIs as time series data, and to compute various filtering functions on such RTDIs.